<!DOCTYPE html>
<html>
<head>
  <meta charset="utf-8">
  
  <title>sql学习记录（sql sever）.md | 蓝湖畔淅淅沥沥的雨</title>
  <meta name="viewport" content="width=device-width, initial-scale=1, maximum-scale=1">
  <meta name="description" content="说明 - 2022-05-05 本篇博客为本人原创, 原发布于CSDN, 在搭建个人博客后使用爬虫批量爬取并挂到个人博客, 出于一些技术原因博客未能完全还原到初始版本(而且我懒得修改), 在观看体验上会有一些瑕疵 ,若有需求会发布重制版总结性新博客。发布时间统一定为1111年11月11日。钦此。 其实是补之前没交的sql作业  1.非常基本的操作  新建数据库 ​ 1create databas">
<meta property="og:type" content="article">
<meta property="og:title" content="sql学习记录（sql sever）.md">
<meta property="og:url" content="http://example.com/1111/11/11/sql%E5%AD%A6%E4%B9%A0%E8%AE%B0%E5%BD%95%EF%BC%88sql%20sever%EF%BC%89/index.html">
<meta property="og:site_name" content="蓝湖畔淅淅沥沥的雨">
<meta property="og:description" content="说明 - 2022-05-05 本篇博客为本人原创, 原发布于CSDN, 在搭建个人博客后使用爬虫批量爬取并挂到个人博客, 出于一些技术原因博客未能完全还原到初始版本(而且我懒得修改), 在观看体验上会有一些瑕疵 ,若有需求会发布重制版总结性新博客。发布时间统一定为1111年11月11日。钦此。 其实是补之前没交的sql作业  1.非常基本的操作  新建数据库 ​ 1create databas">
<meta property="og:locale" content="zh_CN">
<meta property="article:published_time" content="1111-11-11T03:06:11.000Z">
<meta property="article:modified_time" content="2022-05-06T08:20:04.431Z">
<meta property="article:author" content="StarsWhisper">
<meta property="article:tag" content="OldBlog(Before20220505)">
<meta property="article:tag" content="sql">
<meta property="article:tag" content="数据库">
<meta name="twitter:card" content="summary">
  
    <link rel="alternate" href="/atom.xml" title="蓝湖畔淅淅沥沥的雨" type="application/atom+xml">
  
  
    <link rel="icon" href="/favicon.png">
  
  
    <link href="//fonts.googleapis.com/css?family=Source+Code+Pro" rel="stylesheet" type="text/css">
  
  
<link rel="stylesheet" href="/css/style.css">

  
<link rel="stylesheet" href="/plugin/bganimation/bg.css">

  

  <link href="https://cdnjs.cloudflare.com/ajax/libs/KaTeX/0.10.0/katex.min.css" rel="stylesheet" type="text/css">
<meta name="generator" content="Hexo 6.1.0"></head>

<body>
  <div id="container">
    <div id="wrap">
      <div class="outer">
        <div class="widget-wrap mobile-header">
  <h3 class="widget-title"></h3>
  <div class="widget">
    <img class="avatar" src="/images/avatar.png">
    <h2 class="author">StarsWhisper</h2>
    <h3 class="description"></h3>
    <div class="count-box">
      <a href="/archives"><div><strong>75</strong><br>文章</div></a>
      <a href="/categories"><div><strong>31</strong><br>分类</div></a>
      <a href="/tags"><div><strong>56</strong><br>标签</div></a>
    </div>
    <ul class="blog-link">
     
          <a href="/" title="Home">
            <li>主页</li>
          </a>
        
          <a href="/archives" title="Archives">
            <li>归档</li>
          </a>
        
          <a href="/categories" title="Categories">
            <li>分类</li>
          </a>
        
          <a href="/tags" title="Tags">
            <li>标签</li>
          </a>
        
          <a href="/knightabout" title="Knightabout">
            <li>关于</li>
          </a>
        
          <a href="/bridges" title="Bridges">
            <li>传送门</li>
          </a>
        
          <a href="/announcement" title="Announcement">
            <li>公告</li>
          </a>
        
    </ul>
  </div>
</div>

        <section id="main"><article id="post-sql学习记录（sql sever）" class="wow slideInRight article article-type-post" itemscope itemprop="blogPost">
  <div class="article-meta">
    <a href="/1111/11/11/sql%E5%AD%A6%E4%B9%A0%E8%AE%B0%E5%BD%95%EF%BC%88sql%20sever%EF%BC%89/" class="article-date">
  <time class="post-time" datetime="1111-11-11T03:06:11.000Z" itemprop="datePublished">
    <span class="post-month">11月</span><br/>
    <span class="post-day">11</span>
  </time>
</a>
   
  </div>
  <div class="article-inner">
    
    
      <header class="article-header">
        
  
    <h1 class="article-title" itemprop="name">
      sql学习记录（sql sever）.md
    </h1>
  

        <div>
          
  <div class="article-category">
    <a class="article-category-link" href="/categories/%E5%9F%BA%E7%A1%80%E7%9F%A5%E8%AF%86/">基础知识</a>,<a class="article-category-link" href="/categories/%E5%9F%BA%E7%A1%80%E7%9F%A5%E8%AF%86/sql/">sql</a>
  </div>

          
              

          
        </div>
      </header>
    
    <div class="article-entry" itemprop="articleBody">
      
        <h2 id="说明-2022-05-05"><a class="markdownIt-Anchor" href="#说明-2022-05-05"></a> 说明 - 2022-05-05</h2>
<p>本篇博客为本人原创, 原发布于CSDN, 在搭建个人博客后使用爬虫批量爬取并挂到个人博客, 出于一些技术原因博客未能完全还原到初始版本(而且我懒得修改), 在观看体验上会有一些瑕疵 ,若有需求会发布重制版总结性新博客。发布时间统一定为1111年11月11日。钦此。</p>
<p><s>其实是补之前没交的sql作业</s></p>
<h2 id="1非常基本的操作"><a class="markdownIt-Anchor" href="#1非常基本的操作"></a> 1.非常基本的操作</h2>
<h5 id="新建数据库"><a class="markdownIt-Anchor" href="#新建数据库"></a> 新建数据库</h5>
<p>​</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">create</span> database L4C2</span><br></pre></td></tr></table></figure>
<h5 id="新建表"><a class="markdownIt-Anchor" href="#新建表"></a> 新建表</h5>
<p>PID为主键</p>
<p>​</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">create</span> <span class="keyword">table</span> L4C2.dbo.Person</span><br><span class="line">(</span><br><span class="line">	PID <span class="type">nchar</span>(<span class="number">15</span>),</span><br><span class="line">	PName <span class="type">nchar</span>(<span class="number">25</span>),</span><br><span class="line">	PRole <span class="type">nchar</span>(<span class="number">10</span>),</span><br><span class="line">	Lucky <span class="type">int</span>,</span><br><span class="line">	Ammo <span class="type">int</span></span><br><span class="line">	<span class="keyword">primary</span> key(PID)</span><br><span class="line">)</span><br></pre></td></tr></table></figure>
<h5 id="增加列"><a class="markdownIt-Anchor" href="#增加列"></a> 增加列</h5>
<p>​</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">alter</span> <span class="keyword">table</span> L4C2.dbo.Person <span class="keyword">add</span> Ping <span class="type">int</span></span><br></pre></td></tr></table></figure>
<h5 id="修改列"><a class="markdownIt-Anchor" href="#修改列"></a> 修改列</h5>
<p>修改列的数据类型</p>
<p>​</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">alter</span> <span class="keyword">table</span> L4C2.dbo.Person <span class="keyword">alter</span> <span class="keyword">column</span> Lucky <span class="type">varchar</span>(<span class="number">10</span>)</span><br></pre></td></tr></table></figure>
<h5 id="删除列"><a class="markdownIt-Anchor" href="#删除列"></a> 删除列</h5>
<p>​</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">alter</span> <span class="keyword">table</span> L4C2.dbo.Person <span class="keyword">drop</span> <span class="keyword">column</span> Lucky</span><br></pre></td></tr></table></figure>
<h5 id="给表增加行"><a class="markdownIt-Anchor" href="#给表增加行"></a> 给表增加行</h5>
<p>增加多行，给所有列赋值</p>
<p>​</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">insert</span> <span class="keyword">into</span> L4C2.dbo.Person <span class="keyword">values</span></span><br><span class="line">(<span class="number">1234567</span>,<span class="string">&#x27;EVO.T im a&#x27;</span>,<span class="string">&#x27;Ellis&#x27;</span>,<span class="number">700</span>,<span class="number">230</span>),</span><br><span class="line">(<span class="number">1234568</span>,<span class="string">&#x27;EVO.Star&#x27;</span>,<span class="string">&#x27;Smoker&#x27;</span>,<span class="number">0</span>,<span class="number">225</span>),</span><br><span class="line">(<span class="number">1234569</span>,<span class="string">&#x27;Double.Mayuyu&#x27;</span>,<span class="string">&#x27;Coach&#x27;</span>,<span class="number">80</span>,<span class="number">40</span>),</span><br><span class="line">(<span class="number">1234570</span>,<span class="string">&#x27;dec.nv&#x27;</span>,<span class="string">&#x27;Nick&#x27;</span>,<span class="number">0</span>,<span class="number">233</span>),</span><br><span class="line">(<span class="number">1234571</span>,<span class="string">&#x27;kain.nv&#x27;</span>,<span class="string">&#x27;Charger&#x27;</span>,<span class="number">0</span>,<span class="number">125</span>);</span><br></pre></td></tr></table></figure>
<p>给特定列赋值</p>
<p>​</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">insert</span> <span class="keyword">into</span> L4C2.dbo.Person (PID,PName,PRole) <span class="keyword">values</span> (<span class="number">7654321</span>,<span class="string">&#x27;NPC.Bilibili.Deadz&#x27;</span>,<span class="string">&#x27;spectator&#x27;</span>)</span><br></pre></td></tr></table></figure>
<h5 id="创建索引"><a class="markdownIt-Anchor" href="#创建索引"></a> 创建索引</h5>
<p>按照Ping创建降序索引</p>
<p>​</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">create</span> index idx_Ping <span class="keyword">on</span> L4C2.dbo.Person(Ping <span class="keyword">desc</span>)</span><br></pre></td></tr></table></figure>
<p>按照Ammo创建升序索引</p>
<p>​</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">create</span> index idx_Ammo <span class="keyword">on</span> L4C2.dbo.Person(Ping <span class="keyword">asc</span>)</span><br></pre></td></tr></table></figure>
<h5 id="删除索引"><a class="markdownIt-Anchor" href="#删除索引"></a> 删除索引</h5>
<p>​</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">drop</span> index idx_Ping <span class="keyword">on</span> L4C2.dbo.Person</span><br></pre></td></tr></table></figure>
<h2 id="2查询训练"><a class="markdownIt-Anchor" href="#2查询训练"></a> 2.查询训练</h2>
<p>1.查找年级为2001的学生，按学号降序排列</p>
<p>​</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">select</span> STname <span class="keyword">from</span> University.dbo.Student</span><br><span class="line"><span class="keyword">where</span> STgrade <span class="operator">=</span> <span class="number">2001</span></span><br><span class="line"><span class="keyword">order</span> <span class="keyword">by</span> STid <span class="keyword">desc</span></span><br></pre></td></tr></table></figure>
<p>查找年级为2001的学生，按学号升序排列</p>
<p>​</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">select</span> STname <span class="keyword">from</span> University.dbo.Student</span><br><span class="line"><span class="keyword">where</span> STgrade <span class="operator">=</span> <span class="number">2001</span></span><br><span class="line"><span class="keyword">order</span> <span class="keyword">by</span> STid <span class="keyword">asc</span></span><br></pre></td></tr></table></figure>
<p>查询所有选课课程中及格的分数，并将其换算为绩点（60分为1，每增加一分，绩点+0.1）</p>
<p>​</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">select</span> (SCore<span class="number">-60</span>)<span class="operator">/</span><span class="number">10</span><span class="operator">+</span><span class="number">1</span> <span class="keyword">from</span> University.dbo.SC</span><br><span class="line"><span class="keyword">where</span> SCore <span class="operator">&gt;=</span> <span class="number">60</span></span><br></pre></td></tr></table></figure>
<p>查询课时为48或24的课程的名称</p>
<p>​</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">select</span> CRname <span class="keyword">from</span> University.dbo.Course x</span><br><span class="line"><span class="keyword">where</span> x.CRtimes<span class="operator">=</span><span class="number">48</span> <span class="keyword">or</span> x.CRtimes<span class="operator">=</span><span class="number">24</span></span><br></pre></td></tr></table></figure>
<p>查询课程名中带有data的课程的编号</p>
<p>​</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">select</span> CRid <span class="keyword">from</span> University.dbo.Course x</span><br><span class="line"><span class="keyword">where</span> x.CRname <span class="keyword">like</span> <span class="string">&#x27;%data%&#x27;</span></span><br></pre></td></tr></table></figure>
<p>查询所有选课记录中的课程号，不重复显示</p>
<p>​</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">select</span> <span class="keyword">distinct</span> CRid <span class="keyword">from</span> University.dbo.SC x</span><br></pre></td></tr></table></figure>
<p>老师的平均工资</p>
<p>​</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">select</span> <span class="built_in">avg</span>(THsalary) <span class="keyword">from</span> University.dbo.Teacher</span><br></pre></td></tr></table></figure>
<p>查询所有学生编号和平均成绩，按照平均成绩降序排序</p>
<p>​</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">select</span> STid,<span class="built_in">avg</span>(SCore) <span class="keyword">from</span> University.dbo.SC</span><br><span class="line"><span class="keyword">group</span> <span class="keyword">by</span> STid</span><br><span class="line"><span class="keyword">order</span> <span class="keyword">by</span> <span class="built_in">avg</span>(SCore) <span class="keyword">desc</span></span><br></pre></td></tr></table></figure>
<p>统计所有课程的选课人数和平均成绩</p>
<p>​</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">select</span> CRid,<span class="built_in">count</span>(CRid) <span class="string">&#x27;人数&#x27;</span>,<span class="built_in">avg</span>(SCore) <span class="string">&#x27;平均成绩&#x27;</span></span><br><span class="line"><span class="keyword">from</span> University.dbo.SC</span><br><span class="line"><span class="keyword">group</span> <span class="keyword">by</span> CRid</span><br></pre></td></tr></table></figure>
<p>查询至少选修了三门课的学生号</p>
<p>​</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">select</span> STid <span class="keyword">from</span> University.dbo.SC</span><br><span class="line"><span class="keyword">group</span> <span class="keyword">by</span> STid</span><br><span class="line"><span class="keyword">having</span> <span class="built_in">count</span>(<span class="operator">*</span>) <span class="operator">&gt;=</span> <span class="number">3</span></span><br></pre></td></tr></table></figure>
<p>查询学号为2001001的学生选修的课程名和成绩</p>
<p>​</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">select</span> CRname,Score </span><br><span class="line"><span class="keyword">from</span> University.dbo.SC s, University.dbo.Course c</span><br><span class="line"><span class="keyword">where</span> s.CRid <span class="operator">=</span> c.CRid <span class="keyword">and</span> s.STid <span class="operator">=</span> <span class="string">&#x27;2001001&#x27;</span></span><br></pre></td></tr></table></figure>
<p>查询所有选了database课的学生id</p>
<p>​</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">select</span> STid</span><br><span class="line"><span class="keyword">from</span> University.dbo.Course c , University.dbo.SC sc</span><br><span class="line"><span class="keyword">where</span> c.CRname <span class="operator">=</span> <span class="string">&#x27;database&#x27;</span> <span class="keyword">and</span> sc.CRid <span class="operator">=</span> c.CRid</span><br></pre></td></tr></table></figure>
<p>查询所有选了同一课程的学生对</p>
<p>​</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">select</span> <span class="keyword">distinct</span> x.STid A,y.STid B</span><br><span class="line"><span class="keyword">from</span> University.dbo.SC x,University.dbo.SC y</span><br><span class="line"><span class="keyword">where</span> x.CRid <span class="operator">=</span> y.CRid <span class="keyword">and</span> x.STid<span class="operator">&lt;</span>y.STid</span><br></pre></td></tr></table></figure>
<p>查询至少有两人选修的课程id</p>
<p>​</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">select</span> CRid</span><br><span class="line"><span class="keyword">from</span> University.dbo.SC</span><br><span class="line"><span class="keyword">group</span> <span class="keyword">by</span> CRid</span><br><span class="line"><span class="keyword">having</span> <span class="built_in">count</span>(<span class="operator">*</span>) <span class="operator">&gt;=</span><span class="number">2</span></span><br></pre></td></tr></table></figure>
<p>查询跟学号2001001的学生共同上课的学生的编号</p>
<p>​</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">select</span> <span class="keyword">distinct</span> y.STid</span><br><span class="line"><span class="keyword">from</span> University.dbo.SC x, University.dbo.SC y</span><br><span class="line"><span class="keyword">where</span> x.CRid <span class="operator">=</span> y.CRid <span class="keyword">and</span> x.STid <span class="operator">=</span> <span class="string">&#x27;2001001&#x27;</span><span class="keyword">and</span> x.STid <span class="operator">&lt;&gt;</span> y.STid</span><br></pre></td></tr></table></figure>
<p>查询学号为2001001的学生的姓名，以及其所有选课的课程名与成绩</p>
<p>​</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">select</span> <span class="keyword">distinct</span> s.STname,c.CRname,sc.SCore</span><br><span class="line"><span class="keyword">from</span> University.dbo.SC sc , University.dbo.Course c , University.dbo.Student s</span><br><span class="line"><span class="keyword">where</span> s.STid <span class="operator">=</span> sc.STid <span class="keyword">and</span> sc.CRid <span class="operator">=</span> c.CRid <span class="keyword">and</span> s.STid <span class="operator">=</span> <span class="string">&#x27;2001001&#x27;</span></span><br></pre></td></tr></table></figure>
<p>查询所有选了课的学生的基本信息以及课程名和成绩</p>
<p>​</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">select</span> <span class="keyword">distinct</span> s.STid,s.STname,s.STgrade,c.CRname,sc.SCore</span><br><span class="line"><span class="keyword">from</span> University.dbo.SC sc , University.dbo.Course c , University.dbo.Student s</span><br><span class="line"><span class="keyword">where</span> s.STid <span class="operator">=</span> sc.STid <span class="keyword">and</span> sc.CRid <span class="operator">=</span> c.CRid</span><br></pre></td></tr></table></figure>
<p>查询与学号为2001001同年级的学生的信息</p>
<p>​</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">select</span> <span class="operator">*</span></span><br><span class="line"><span class="keyword">from</span> University.dbo.Student</span><br><span class="line"><span class="keyword">where</span> STgrade <span class="operator">=</span> (</span><br><span class="line">	<span class="keyword">select</span> STgrade</span><br><span class="line">	<span class="keyword">from</span> University.dbo.Student</span><br><span class="line">	<span class="keyword">where</span> STid <span class="operator">=</span> <span class="string">&#x27;2001001&#x27;</span></span><br><span class="line">)</span><br></pre></td></tr></table></figure>
<p>​</p>
<p>查询所有选了课的学生信息</p>
<p>​</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">select</span> <span class="operator">*</span></span><br><span class="line"><span class="keyword">from</span> University.dbo.Student</span><br><span class="line"><span class="keyword">where</span> STid <span class="keyword">in</span> (</span><br><span class="line">	<span class="keyword">select</span> STid</span><br><span class="line">	<span class="keyword">from</span> University.dbo.SC</span><br><span class="line">)</span><br></pre></td></tr></table></figure>
<p>​</p>
<p>查询没有人选的课程的编号</p>
<p>​</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">select</span> CRid</span><br><span class="line"><span class="keyword">from</span> University.dbo.Course</span><br><span class="line"><span class="keyword">where</span> CRid <span class="keyword">not</span> <span class="keyword">in</span> (</span><br><span class="line">	<span class="keyword">select</span> CRid</span><br><span class="line">	<span class="keyword">from</span> University.dbo.SC</span><br><span class="line">)</span><br></pre></td></tr></table></figure>
<p>查询所有选了课程名为cpp的课程的学生姓名</p>
<p>​</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">select</span> STname</span><br><span class="line"><span class="keyword">from</span> University.dbo.Student</span><br><span class="line"><span class="keyword">where</span> STid <span class="keyword">in</span> (</span><br><span class="line">	<span class="keyword">select</span> STid</span><br><span class="line">	<span class="keyword">from</span> University.dbo.SC</span><br><span class="line">	<span class="keyword">where</span> CRid <span class="keyword">in</span>(</span><br><span class="line">		<span class="keyword">select</span> CRid</span><br><span class="line">		<span class="keyword">from</span> University.dbo.Course</span><br><span class="line">		<span class="keyword">where</span> CRname <span class="operator">=</span> <span class="string">&#x27;cpp&#x27;</span></span><br><span class="line">	)</span><br><span class="line">)</span><br></pre></td></tr></table></figure>
<p>​</p>
<p>查询成绩最差的成绩状况</p>
<p>​</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">select</span> <span class="operator">*</span> <span class="keyword">from</span> University.dbo.SC x</span><br><span class="line"><span class="keyword">where</span> x.SCore <span class="operator">&lt;=</span> <span class="keyword">ALL</span>(</span><br><span class="line">	<span class="keyword">select</span> SCore <span class="keyword">from</span> SC <span class="keyword">where</span> SCore <span class="keyword">is</span> <span class="keyword">not</span> <span class="keyword">NULL</span></span><br><span class="line">)</span><br></pre></td></tr></table></figure>
<p>查询课时跟python或java一样长的课程名</p>
<p>​</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">select</span> CRname <span class="keyword">from</span> University.dbo.Course</span><br><span class="line"><span class="keyword">where</span> CRtimes <span class="operator">=</span> <span class="keyword">some</span>(</span><br><span class="line"><span class="keyword">select</span> CRtimes <span class="keyword">from</span> University.dbo.Course x</span><br><span class="line"><span class="keyword">where</span> CRname <span class="operator">=</span> <span class="string">&#x27;Java&#x27;</span> <span class="keyword">or</span> CRname <span class="operator">=</span> <span class="string">&#x27;Python&#x27;</span></span><br><span class="line">)</span><br></pre></td></tr></table></figure>
<p>查询选了1001号课的学生名字</p>
<p>​</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">select</span> STname <span class="keyword">from</span> University.dbo.Student y</span><br><span class="line"><span class="keyword">where</span> <span class="keyword">exists</span>(</span><br><span class="line">	<span class="keyword">select</span> <span class="operator">*</span> <span class="keyword">from</span> University.dbo.SC x</span><br><span class="line">	<span class="keyword">where</span> x.CRid <span class="operator">=</span> <span class="string">&#x27;1001&#x27;</span> <span class="keyword">and</span> y.STid <span class="operator">=</span> x.STid</span><br><span class="line">)</span><br></pre></td></tr></table></figure>
<p>查询选修了所有课程的学生的姓名</p>

      
    </div>
    <footer class="article-footer">
      <a data-url="http://example.com/1111/11/11/sql%E5%AD%A6%E4%B9%A0%E8%AE%B0%E5%BD%95%EF%BC%88sql%20sever%EF%BC%89/" data-id="cl2uhoebp000ne4j34nz42qne" class="article-share-link">分享</a>
      
      
  <ul class="article-tag-list" itemprop="keywords"><li class="article-tag-list-item"><a class="article-tag-list-link" href="/tags/OldBlog-Before20220505/" rel="tag">OldBlog(Before20220505)</a></li><li class="article-tag-list-item"><a class="article-tag-list-link" href="/tags/sql/" rel="tag">sql</a></li><li class="article-tag-list-item"><a class="article-tag-list-link" href="/tags/%E6%95%B0%E6%8D%AE%E5%BA%93/" rel="tag">数据库</a></li></ul>

    </footer>
  </div>
  
    
<nav id="article-nav">
  
    <a href="/1111/11/11/python%20graphviz%E7%9A%84%E4%BD%BF%E7%94%A8%20%20%E6%94%B9%E6%97%A5%E5%A1%AB%E5%9D%91/" id="article-nav-newer" class="article-nav-link-wrap">
      <strong class="article-nav-caption">上一篇</strong>
      <div class="article-nav-title">
        
          python graphviz的使用  改日填坑.md
        
      </div>
    </a>
  
  
    <a href="/1111/11/11/python%20%E7%AE%80%E5%8D%95%E7%9A%84%E9%A2%9C%E8%89%B2%E5%BA%8F%E5%88%97%E7%94%9F%E6%88%90%E5%99%A8/" id="article-nav-older" class="article-nav-link-wrap">
      <strong class="article-nav-caption">下一篇</strong>
      <div class="article-nav-title">python 简单的颜色序列生成器.md</div>
    </a>
  
</nav>

  
</article>



</section>
        
          <aside id="sidebar">
  
    <div class="widget-wrap">
  <h3 class="widget-title"></h3>
  <div class="widget">
    <h1 class="blog-title">蓝湖畔淅淅沥沥的雨</h1>
    <h2 class="blog-subtitle">All tragedy erased, I see only wonders...</h2>
    <ul class="blog-link">
     
          <a href="/" title="Home">
            <li>主页</li>
          </a>
        
          <a href="/archives" title="Archives">
            <li>归档</li>
          </a>
        
          <a href="/categories" title="Categories">
            <li>分类</li>
          </a>
        
          <a href="/tags" title="Tags">
            <li>标签</li>
          </a>
        
          <a href="/knightabout" title="Knightabout">
            <li>关于</li>
          </a>
        
          <a href="/bridges" title="Bridges">
            <li>传送门</li>
          </a>
        
          <a href="/announcement" title="Announcement">
            <li>公告</li>
          </a>
        
    </ul>
  </div>
</div>

  
    <div class="widget-wrap">
  <h3 class="widget-title"></h3>
  <div class="widget">
    <img class="avatar" src="/images/avatar.png">
    <h2 class="author">StarsWhisper</h2>
    <h3 class="description"></h3>
    <div class="count-box">
      <a href="/archives"><div><strong>75</strong><br>文章</div></a>
      <a href="/categories"><div><strong>31</strong><br>分类</div></a>
      <a href="/tags"><div><strong>56</strong><br>标签</div></a>
    </div>



    <div class="social-link">
      
        <a class="hvr-bounce-in" href="https://github.com/Wldcmzy" target="_blank" title="Github">
          Github
        </a>
      
        <a class="hvr-bounce-in" href="https://blog.csdn.net/wldcmzy" target="_blank" title="CSDN">
          CSDN
        </a>
      
        <a class="hvr-bounce-in" href="https://space.bilibili.com/83743701" target="_blank" title="bilibili(无技术和学习内容)">
          bilibili(无技术和学习内容)
        </a>
      
    </div>

    <div class="friend-link">
      <h2>友情链接</h2>
      
        <a class="hvr-bounce-in" href="https://shanamaid.github.io/" target="_blank" title="夏娜主题作者的博客">
          夏娜主题作者的博客
        </a>
      
    </div>
  </div>
</div>

  
</aside>
        
      </div>
      <footer id="footer">
  
  <div class="outer">
    <div id="footer-info" class="inner">
      &copy;2021 - 2022 StarsWhisper<br>
      由<a href="http://hexo.io/" target="_blank">Hexo</a>强力驱动 | 
      主题-<a target="_blank" rel="noopener" href="https://github.com/ShanaMaid/hexo-theme-shana">Shana</a>(但是魔改)
      
    </div>
    
  </div>
</footer>
    </div>
    

<script src="//apps.bdimg.com/libs/jquery/2.1.4/jquery.min.js"></script>
<script src="//apps.bdimg.com/libs/wow/0.1.6/wow.min.js"></script>
<script>
new WOW().init();
</script>   


  
<link rel="stylesheet" href="/plugin/fancybox/jquery.fancybox.css">

  
<script src="/plugin/fancybox/jquery.fancybox.pack.js"></script>




  
<link rel="stylesheet" href="/plugin/galmenu/GalMenu.css">

  
<script src="/plugin/galmenu/GalMenu.js"></script>

  <div class="GalMenu GalDropDown">
      <div class="circle" id="gal">
        <div class="ring">
          
            <a href="/announcement" title="" class="menuItem">公告</a>
          
            <a href="/tags" title="" class="menuItem">标签</a>
          
            <a href="/categories" title="" class="menuItem">分类</a>
          
            <a href="/archives" title="" class="menuItem">归档</a>
          
            <a href="/knightabout" title="" class="menuItem">关于</a>
          
            <a href="/bridges" title="" class="menuItem">传送门</a>
          
        </div>
        
          <audio id="audio" src="#"></audio>
        
      </div> 
</div>
<div id="overlay" style="opacity: 1; cursor: pointer;"></div>
  <script type="text/javascript">var items = document.querySelectorAll('.menuItem');
    for (var i = 0,
    l = items.length; i < l; i++) {
      items[i].style.left = (50 - 35 * Math.cos( - 0.5 * Math.PI - 2 * (1 / l) * i * Math.PI)).toFixed(4) + "%";
      items[i].style.top = (50 + 35 * Math.sin( - 0.5 * Math.PI - 2 * (1 / l) * i * Math.PI)).toFixed(4) + "%"
    }</script>
<script type="text/javascript">
  $(document).ready(function() {
    $('body').GalMenu({
      'menu': 'GalDropDown'
    })
  });
</script>

  <section class="hidden-xs"> 
  <ul class="cb-slideshow"> 
    <li><span>苟利</span></li> 
    <li><span>国家</span></li> 
    <li><span>生死以</span></li> 
    <li><span>岂能</span></li> 
    <li><span>祸福</span></li> 
    <li><span>趋避之</span></li> 
  </ul>
</section>

<script src="/js/script.js"></script>




  </div>
</body>
</html>